package by.bsu.touragency.db.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.GregorianCalendar;

import org.apache.log4j.Logger;

import by.bsu.touragency.db.beans.City;
import by.bsu.touragency.db.beans.Transfer;
import by.bsu.touragency.db.connection.DBConnector;

public class TransferDAO extends AbstractDAO {

	public static final String SQL_TRANSFER_CREATE = " INSERT INTO tourAgencyDB.transfer (dateFrom, dateTo, cityId) VALUES (? , ?, ?);";
	public static final String SQL_TRANSFER_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.transfer WHERE dateTo = ? AND dateFrom = ? AND cityId=?;";
	public static final String SQL_TRANSFER_IS_EXIST = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.transfer WHERE dateTo = ? AND dateFrom = ? AND cityId = ? ;";
	public static final String SQL_TRANSFER_READ_BY_ID = "SELECT * FROM tourAgencyDB.transfer WHERE (id= ?);";
	public static final String SQL_TRANSFER_UPDATE_DATETO = "UPDATE tourAgencyDB.transfer  SET dateTo = ? WHERE (id = ?);";
	public static final String SQL_TRANSFER_UPDATE_DATEFROM = "UPDATE tourAgencyDB.transfer  SET dateFrom = ? WHERE (id = ?);";
	public static final String SQL_TRANSFER_DELETE = "DELETE FROM tourAgencyDB.transfer WHERE id = ?";
	public static final String SQL_TRANSFER_IS_USED = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.tour WHERE transferId = ? ;";
	private static final String SQL_TRANSFER_READ_BY_DATES_AND_CITY = "SELECT * FROM tourAgencyDB.transfer WHERE (dateTo= ?) AND (dateFrom= ?) AND (cityId=?);";

	private static Logger logger = Logger.getLogger("log");

	private boolean isUsed(int id) {

		logger.info("Is this transfer in use");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSFER_IS_USED);
			int n;
			try {// 3
				statement.setLong(1, id);
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (n > 0) {
				logger.debug("This transfer is in use");
				return true;
			} else {
				logger.debug("This transfer is NOT in use");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	public boolean isExistByDatesAndCity(Transfer transfer) {
		logger.info("Is this transfer exist by Dates & City");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSFER_IS_EXIST);
			int n;
			try {// 3
				java.sql.Date date = new Date(transfer.getDateFrom()
						.getTimeInMillis());
				statement.setDate(2, date);
				date = new Date(transfer.getDateTo().getTimeInMillis());
				statement.setDate(1, date);
				statement.setInt(3, transfer.getCity().getId());

				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");

			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (n > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}

	}

	// C
	public Transfer create(Transfer transfer) {
		if (!isExistByDatesAndCity(transfer)) {
			logger.info("Create new transfer");
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TRANSFER_CREATE);
				java.sql.Date date = new Date(transfer.getDateTo()
						.getTimeInMillis());
				statement.setDate(2, date);
				date = new Date(transfer.getDateFrom().getTimeInMillis());
				statement.setDate(1, date);
				statement.setLong(3, transfer.getCity().getId());
				statement.executeUpdate();

				statement = connector
						.getPreparedStatement(SQL_TRANSFER_CREATE_GET_ID);
				date = new Date(transfer.getDateTo().getTimeInMillis());
				statement.setDate(1, date);
				date = new Date(transfer.getDateFrom().getTimeInMillis());
				statement.setDate(2, date);
				statement.setLong(3, transfer.getCity().getId());
				resultSet = statement.executeQuery();

				if (resultSet.next()) {
					transfer.setId(resultSet.getInt("id"));
				}
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
				e.printStackTrace();
			} catch (Exception e) {
				logger.error("Undefined exeption");
				e.printStackTrace();
			} finally {
				logger.debug("Close connection");
				close();
			}
		} else {
			transfer = readByDatesAndCity(transfer);

		}
		return transfer;

	}

	// R
	public Transfer readByID(int id) {
		logger.info("Read transfer by id");
		Transfer transfer = new Transfer();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSFER_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {

				transfer.setId(resultSet.getInt("id"));

				GregorianCalendar dateTo = new GregorianCalendar();
				java.sql.Date date = resultSet.getDate("dateTo");
				dateTo.setTimeInMillis(date.getTime());
				transfer.setDateTo(dateTo);

				GregorianCalendar dateFrom = new GregorianCalendar();
				date = resultSet.getDate("dateFrom");
				dateFrom.setTimeInMillis(date.getTime());
				transfer.setDateFrom(dateFrom);

				City city = new City();
				CityDAO cd = new CityDAO();
				city = cd.readById(resultSet.getInt("cityId"));
				transfer.setCity(city);
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return transfer;
	}

	private Transfer readByDatesAndCity(Transfer transfer) {
		logger.info("Read transfer by ByDatesAndCity");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSFER_READ_BY_DATES_AND_CITY);

			java.sql.Date date = new Date(transfer.getDateTo()
					.getTimeInMillis());
			statement.setDate(1, date);
			date = new Date(transfer.getDateFrom().getTimeInMillis());
			statement.setDate(2, date);
			statement.setInt(3, transfer.getCity().getId());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				transfer.setId(resultSet.getInt("id"));
				System.out.println("transferDAO" + transfer.getId());
			}
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return transfer;
	}

	// U
	public void updateDateTo(Transfer transfer) {
		logger.info("Update DateTo in transfer");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSFER_UPDATE_DATETO);
			java.sql.Date date = new Date(transfer.getDateTo()
					.getTimeInMillis());
			statement.setDate(1, date);
			statement.setInt(2, transfer.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	public void updateDateFrom(Transfer transfer) {
		logger.info("Update DateFrom in transfer");
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_TRANSFER_UPDATE_DATETO);
			java.sql.Date date = new Date(transfer.getDateFrom()
					.getTimeInMillis());
			statement.setDate(1, date);
			statement.setInt(2, transfer.getId());
			statement.executeUpdate();
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
	}

	// D
	public void delete(Transfer transfer) {
		logger.info("Delete this transfer");
		if (!isUsed(transfer.getId())) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TRANSFER_DELETE);
				statement.setInt(1, transfer.getId());
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}

	public void deleteById(int id) {
		logger.info("Delete this transfer by id");
		if (!isUsed(id)) {
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_TRANSFER_DELETE);
				statement.setInt(1, id);
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}

}
